<?php

/*
 * site/database_fct.php
 *
 */

class SiteDBFct
{
	/*
	 * Label related
	 */
	// label is id only: no sens to search over names which are not unique
	public static function isLabelExist( $label )
	{
		//if( is_int($label ) )
			$sql = 'SELECT 1 FROM `Label` WHERE id='.$label;
		/*
		else
		{
			$label = db()->escapeStr( $label );
			$sql = 'SELECT 1 FROM `Label` WHERE name="'.$label.'"';
		}
		*/

		if( !($result = db()->select($sql)) )
			setError( 'isLabelExist: db error: '.db()->lastError() );

		if( $result->rowCount() >= 1 )
			return true;
		return false;
	}

	// label is id or name
	public static function getLabel( $label )
	{
	}

	// TODO: create a find function

	public static function addLabel( $name )
	{
		$sql = 'SELECT MAX(id) FROM `Label`';
		if( !($result = db()->select($sql)) )
		{
			setError( 'addLabel: db error: '.db()->lastError() );
			return -1;
		}

		$nextid = $result->getVal( 'MAX(id)' ) + 1;
		$name = db()->escapeStr( $name );
		$sql = 'INSERT INTO `Label` (id,name) VALUES ('.$nextid.',"'.$name.'")';
		if( !db()->execute($sql) )
		{
			setError( 'addLabel: db error: '.db()->lastError() );
			return -1;
		}

		return $nextid;
	}

	public static function updateLabel( $id, $name )
	{
		$name = db()->escapeStr( $name );
		$sql = 'UPDATE `Label` SET name="'.$name.'" WHERE id='.$id;

		if( !db()->execute($sql) )
		{
			setError( 'updateLabel: db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	public static function removeLabel( $id )
	{
		$sql = 'DELETE FROM `Label` WHERE id='.$id;
		if( !db()->execute($sql) )
		{
			setError( 'removeLabel: db error: '.db()->lastError() );
			return false;
		}

		$sql = 'DELETE FROM `RelLabelLink` WHERE idLabel='.$id;
		if( !db()->execute($sql) )
		{
			setError( 'removeLabel: db error: '.db()->lastError() );
			return false;
		}

		return true;
	}

	public static function listLabels()
	{
		$sql = 'SELECT id,name FROM `Label` ORDER BY name';
		if( !($result = db()->select($sql)) )
		{
			setError( 'listLabels: db error: '.db()->lastError() );
			return null;
		}

		$r = array();
		while( ($row = $result->getNext()) )
		{
			array_push( $r, array( 'id'=>(int)$row['id'], 'name'=>$row['name'] ) );
		}

		return $r;
	}

	/*
	 * Links related
	 */
	public static function isLinkExists( $id )
	{
		$sql = 'SELECT 1 FROM `Link` WHERE id='.$id;

		if( !($result = db()->select($sql)) )
			setError( 'isLabelExist: db error: '.db()->lastError() );

		if( $result->rowCount() >= 1 )
			return true;
		return false;
	}

	public static function getLink( $id )
	{
		$sql = 'SELECT id, name, url, description FROM `Link` WHERE id='.$id;
		if( !($resultLink = db()->select( $sql )) )
		{
			setError( 'getLink: db error: '.db()->lastError() );
			return null;
		}

		$sql = 'SELECT id, name FROM `Label` AS l, `RelLabelLink` AS r WHERE l.id=r.idLabel AND r.idLink='.$id;
		if( !($resultLabels = db()->select( $sql )) )
		{
			setError( 'getLink: db error: '.db()->lastError() );
			return null;
		}

		$r = array();
		$r['id'] = $resultLink->getVal( 'id' );
		$r['name'] = $resultLink->getVal( 'name' );
		$r['url'] = $resultLink->getVal( 'url' );
		$r['description'] = $resultLink->getVal( 'description' );

		$r['labels'] = array();
		while( ($row =$resultLabels->getNext()) )
		{
			array_push( $r['labels'], array( 'id'=>(int)$row['id'], 'name'=>$row['name'] ) );
		}

		return $r;
	}

	public static function addLink( $name, $url, $desc, $labelIds = null )
	{
		$sql = 'SELECT MAX(id) FROM `Link`';
		if( !($result = db()->select($sql)) )
		{
			setError( 'addLink: db error: '.db()->lastError() );
			return -1;
		}

		$nextid = $result->getVal( 'MAX(id)' ) + 1;
		$name = db()->escapeStr( $name );
		$url = db()->escapeStr( $url );
		$desc = db()->escapeStr( $desc );

		$sql = 'INSERT INTO `Link` (id,name,url,description) VALUES ('.$nextid.',"'.$name.'","'.$url.'","'.$desc.'")';
		if( !db()->execute($sql) )
		{
			setError( 'addLink: db error: '.db()->lastError() );
			return -1;
		}

		if( !is_null($labelIds) && count($labelIds) )
		{
			$sql = 'INSERT INTO `RelLabelLink` (idLabel,idLink) VALUES ';
			$nb = count( $labelIds ) - 1;
			for( $i = 0; $i <= $nb; $i++ )
			{
				$sql .= '('.$labelIds[$i].','.$nextid.')';
				if( $i < $nb ) $sql .= ', ';
			}

			if( !db()->execute($sql) )
			{
				setError( 'addLink: db error: '.db()->lastError() );
				return -1;
			}
		}

		return $nextid;
	}

	public static function updateLink( $id, $name, $url, $desc, $labelIds=null )
	{
		$name = db()->escapeStr( $name );
		$sql = 'UPDATE `Link` SET name="'.$name.'", url="'.$url.'", description="'.$desc.'" WHERE id='.$id;

		if( !db()->execute($sql) )
		{
			setError( 'updateLink: db error: '.db()->lastError() );
			return false;
		}

		$sql = 'DELETE FROM `RelLabelLink` WHERE idLink='.$id;
		if( !db()->execute($sql) )
		{
			setError( 'updateLink: db error: '.db()->lastError() );
			return false;
		}

		if( !is_null($labelIds) && count($labelIds) )
		{
			$sql = 'INSERT INTO `RelLabelLink` (idLabel,idLink) VALUES ';
			$nb = count( $labelIds ) - 1;
			for( $i = 0; $i <= $nb; $i++ )
			{
				$sql .= '('.$labelIds[$i].','.$id.')';
				if( $i < $nb ) $sql .= ', ';
			}

			if( !db()->execute($sql) )
			{
				setError( 'updateLink: db error: '.db()->lastError() );
				return -1;
			}
		}

		return true;
	}

	public static function removeLink( $id )
	{
		$sql = 'DELETE FROM `Link` WHERE id='.$id;
		if( !db()->execute($sql) )
		{
			setError( 'removeLink: db error: '.db()->lastError() );
			return false;
		}

		$sql = 'DELETE FROM `RelLabelLink` WHERE idLink='.$id;
		if( !db()->execute($sql) )
		{
			setError( 'removeLink: db error: '.db()->lastError() );
			return false;
		}

		return true;
	}

	public static function listLinks( $labelId = null )
	{
		if( is_null($labelId) )
			$sql = 'SELECT id,name,url,description FROM `Link`';
		else
			$sql = 'SELECT id,name,url,description FROM `Link` AS l, `RelLabelLink` AS r WHERE r.idLabel='.$labelId.' AND r.idLink=l.id';

		if( !($result = db()->select($sql)) )
		{
			setError( 'listLinks: db error: '.db()->lastError() );
			return null;
		}

		$r = array();
		while( ($row = $result->getNext()) )
		{
			array_push( $r, array( 'id'=>(int)$row['id'], 'name'=>$row['name'], 'url'=>$row['url'], 'description'=>$row['description'] ) );
		}

		return $r;
	}

	public static function listUnlabelledLinks()
	{
		// TODO: look to retreive idLink only one per idLink!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

		$sql = 'SELECT id,name,url,description FROM `Link` AS l WHERE l.id not in (SELECT idLink FROM `RelLabelLink`)';

		if( !($result = db()->select($sql)) )
		{
			setError( 'listLinks: db error: '.db()->lastError() );
			return null;
		}

		$r = array();
		while( ($row = $result->getNext()) )
		{
			array_push( $r, array( 'id'=>(int)$row['id'], 'name'=>$row['name'], 'url'=>$row['url'], 'description'=>$row['description'] ) );
		}

		return $r;
	}

	public static function hasHomeText( $lang )
	{
		$lang = db()->escapeStr( $lang );

		$sql = 'SELECT 1 FROM `Home` WHERE lang="'.$lang.'"';
		$result = db()->select( $sql );
		if( !$result )
		{
			setError( "SiteDBFct.hasHomeText: db error: ".db()->lastError() );
			return false;
		}

		if( $result->rowCount() == 0 ) return false;
		return true;
	}

	public static function insertHomeText( $lang, $text )
	{
		//$lang = db()->escapeStr( $lang );
		//$text = db()->escapeStr( $text );

		$sql = 'INSERT INTO Home (lang,txt) VALUES ( "'.$lang.'","'.$text.'" )';
		if( !db()->execute($sql) )
		{
			setError( "SiteDBFct.insertHomeText: db error: ".db()->lastError() );
			return false;
		}

		return true;
	}

	public static function updateHomeText( $lang, $text )
	{
		//$lang = db()->escapeStr( $lang );
		//$text = db()->escapeStr( $text );

		$sql = 'UPDATE `Home` SET txt="'.$text.'" WHERE lang="'.$lang.'"';
		if( !db()->execute($sql) )
		{
			setError( "SiteDBFct.updateHomeText: db error: ".db()->lastError() );
			return false;
		}

		return true;
	}

	public static function getHomeText( $lang )
	{
		$sql = 'SELECT txt FROM `Home` WHERE lang="'.$lang.'"';
		$result = db()->select( $sql );
		if( !$result )
		{
			setError( 'SiteDBFct.getHomeText: db error: '.db()->lastError() );
			return null;
		}

		if( $result->rowCount() == 0 )
		{
			return "";
		}

		return $result->getVal( 'txt' );
	}

};




?>
